﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace GBH.Helper
{
    public class ExcelReader
    {
        string _filePath;

        public ExcelReader(string filePaht)
        {
            if (string.IsNullOrEmpty(filePaht))
                new Exception("file path can't emply");

            _filePath = filePaht;
        }

        public List<Models.sell_out> ReadFileToSellOut()
        {
            List<Models.sell_out> sellouts = new List<Models.sell_out>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(_filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 2; i <= row ; i++)
                        {
                            var qtyInt = 0;
                            var qty = currentWorksheet.Cells[i, 5].Value.ToString();
                            if (qty.Contains('.'))
                            {
                                qty = qty.Remove(qty.IndexOf('.'));
                            }
                            decimal valueDecimal = 0;
                            var sellout = new Models.sell_out(){product_id =  currentWorksheet.Cells[i, 1].Value.ToString(),
                                product_name = currentWorksheet.Cells[i, 2].Value.ToString(),
                                 location_id = currentWorksheet.Cells[i, 3].Value.ToString(),
                                                                qty = qty == null || !Int32.TryParse(qty, out qtyInt) ? 0 : Convert.ToInt32(qty),
                                 unit_type_name=  currentWorksheet.Cells[i, 6].Value.ToString(),
                                 value =  currentWorksheet.Cells[i, 7].Value == null || !decimal.TryParse(currentWorksheet.Cells[i, 7].Value.ToString(), out valueDecimal) ? 0 : Convert.ToDecimal(currentWorksheet.Cells[i, 7].Value),
                                 date_time =  currentWorksheet.Cells[i, 8].Value == null ? DateTime.MinValue : Convert.ToDateTime(currentWorksheet.Cells[i, 8].Value)
                            };
                            sellouts.Add(sellout);
                        }
                        return sellouts;
                    }
                }
            }
            return sellouts;
        }

        public List<Models.stock> ReadFileToStockBalance()
        {
            List<Models.stock> stocks = new List<Models.stock>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(_filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 2; i <= row; i++)
                        {
                            var qtyInt = 0;
                            decimal valueDecimal = 0;
                            var qty = currentWorksheet.Cells[i, 5].Value.ToString();
                            if (qty.Contains('.'))
                            {
                                qty = qty.Remove(qty.IndexOf('.'));
                            }
                            var st = new Models.stock()
                            {
                                product_id = currentWorksheet.Cells[i, 1].Value.ToString(),
                                product_name = currentWorksheet.Cells[i, 2].Value.ToString(),
                                location_id = currentWorksheet.Cells[i, 3].Value.ToString(),
                                qty = currentWorksheet.Cells[i, 5].Value == null || !int.TryParse(qty, out qtyInt) ? 0 : Convert.ToInt32(qty),
                                unit_type_name = currentWorksheet.Cells[i, 6].Value.ToString(),
                                value = currentWorksheet.Cells[i, 7].Value == null || !decimal.TryParse(currentWorksheet.Cells[i, 7].Value.ToString(), out valueDecimal) ? 0 : Convert.ToDecimal(currentWorksheet.Cells[i, 7].Value),
                            };
                            if (st.qty < 0)
                            {
                                st.qty = 0;
                            }
                            stocks.Add(st);
                        }
                        return stocks;
                    }
                }
            }
            return stocks;
        }

        public List<Models.material> ReadFileToMaterialExtension(){
            List<Models.material> mats = new List<Models.material>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(_filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 2; i <= row; i++)
                        {
                            var qtyInt = 0;
                            var mat = new Models.material()
                            {
                                mmatno = currentWorksheet.Cells[i, 1].Value == null ? "" : currentWorksheet.Cells[i, 1].Value.ToString(),
                                npibarcode = currentWorksheet.Cells[i, 5].Value == null ? "" : currentWorksheet.Cells[i, 5].Value.ToString(),
                                mnamethai = currentWorksheet.Cells[i, 6].Value == null ? "" : currentWorksheet.Cells[i, 6].Value.ToString(),
                                sizeunit = currentWorksheet.Cells[i, 2].Value == null ? "" : currentWorksheet.Cells[i, 2].Value.ToString(),
                                packageunit = currentWorksheet.Cells[i, 4].Value == null ? "" : currentWorksheet.Cells[i, 4].Value.ToString(),
                                quantitypersetunit = currentWorksheet.Cells[i, 3].Value == null || !int.TryParse(currentWorksheet.Cells[i, 3].Value.ToString(), out qtyInt) ? 0 : Convert.ToInt32(currentWorksheet.Cells[i, 12].Value),
                            };
                            mats.Add(mat);
                        }
                        return mats;
                    }
                }
            }
            return mats;
        }

        public List<Models.Branch> ReadFileToBranchlExtension()
        {
            List<Models.Branch> branchs = new List<Models.Branch>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(_filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 2; i <= row; i++)
                        {
                            var isEnable = true;
                            var qtyInt = 0;
                            var branch = new Models.Branch()
                            {
                                mbranchno = currentWorksheet.Cells[i, 1].Value == null ? "" : currentWorksheet.Cells[i, 1].Value.ToString(),
                                mcustno = currentWorksheet.Cells[i, 2].Value == null ? "" : currentWorksheet.Cells[i, 2].Value.ToString(),
                                mprovdesc = currentWorksheet.Cells[i, 3].Value == null ? "" : currentWorksheet.Cells[i, 3].Value.ToString(),
                                mprovtrans = currentWorksheet.Cells[i, 4].Value == null ? "" : currentWorksheet.Cells[i, 4].Value.ToString(),
                                mprovtransdesc = currentWorksheet.Cells[i, 5].Value == null ? "" : currentWorksheet.Cells[i, 5].Value.ToString(),
                                mprovtran_grp = currentWorksheet.Cells[i, 6].Value == null || !int.TryParse(currentWorksheet.Cells[i, 6].Value.ToString(), out qtyInt) ? 0 : Convert.ToInt32(currentWorksheet.Cells[i, 8].Value),
                                isenable = currentWorksheet.Cells[i, 7].Value == null || !bool.TryParse(currentWorksheet.Cells[i, 7].Value.ToString(), out isEnable) ? true : Convert.ToBoolean(currentWorksheet.Cells[i, 9].Value),
                                mprovice =""
                            };
                            branchs.Add(branch);
                        }
                        return branchs;
                    }
                }
            }
            return branchs;
        }

        public List<Models.daysellMinMax> ReadFileToMinMax()
        {
            List<Models.daysellMinMax> mats = new List<Models.daysellMinMax>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(_filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 2; i <= row; i++)
                        {
                            var qtyInt = 0;
                            var isEnable = true;
                            var mat = new Models.daysellMinMax()
                            {
                                product_id = currentWorksheet.Cells[i, 1].Value == null ? "" : currentWorksheet.Cells[i, 1].Value.ToString(),
                                location_id = currentWorksheet.Cells[i, 2].Value == null ? "" : currentWorksheet.Cells[i, 2].Value.ToString().Trim(),
                                min = currentWorksheet.Cells[i, 3].Value == null || !int.TryParse(currentWorksheet.Cells[i, 3].Value.ToString(), out qtyInt) ? 0 : Convert.ToInt32(currentWorksheet.Cells[i, 3].Value),
                                max = currentWorksheet.Cells[i, 4].Value == null || !int.TryParse(currentWorksheet.Cells[i, 4].Value.ToString(), out qtyInt) ? 0 : Convert.ToInt32(currentWorksheet.Cells[i, 4].Value),
                                rop = currentWorksheet.Cells[i, 5].Value == null || !int.TryParse(currentWorksheet.Cells[i, 5].Value.ToString(), out qtyInt) ? 0 : Convert.ToInt32(currentWorksheet.Cells[i, 5].Value),
                                isEnable = currentWorksheet.Cells[i, 6].Value == null || !bool.TryParse(currentWorksheet.Cells[i, 6].Value.ToString(), out isEnable) ? true : Convert.ToBoolean(currentWorksheet.Cells[i, 6].Value),
                            };
                            mats.Add(mat);
                        }
                        return mats;
                    }
                }
            }
            return mats;
        }

        public List<Models.MaterialLocation> ReadFileToMatBranch()
        {
            List<Models.MaterialLocation> mats = new List<Models.MaterialLocation>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(_filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 2; i <= row; i++)
                        {
                            var isEnable = true;
                            var mat = new Models.MaterialLocation()
                            {
                                MatNo = currentWorksheet.Cells[i, 2].Value == null ? "" : currentWorksheet.Cells[i, 2].Value.ToString(),
                                LocationNo = currentWorksheet.Cells[i, 3].Value == null ? "" : currentWorksheet.Cells[i, 3].Value.ToString(),
                                IsEnable = currentWorksheet.Cells[i, 4].Value == null || !bool.TryParse(currentWorksheet.Cells[i, 4].Value.ToString(), out isEnable) ? true : Convert.ToBoolean(currentWorksheet.Cells[i, 4].Value),
                            };
                            mats.Add(mat);
                        }
                        return mats;
                    }
                }
            }
            return mats;
        }
    }
}